//
// Copyright (c) 2014, 保留所有权利。
//
// ContractSync.java
//
// 作    者：王红燕
// 当前版本：1.0
// 2014 下午1:16:13
//
// 
//
package com.vanke.agent.sync;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;


import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.Cfg;
import com.vanke.agent.bean.MemInfo;
import com.vanke.agent.common.CstUtil;

/**
 *
 */
public class MemberSync extends AgentSync {
	Logger log = Logger.getLogger(MemberSync.class);
	String table = "h_Member";
	String fields = C.FIELDS_MEM;

	@Override
	public void sync() {
		log.info("=============sync new h_Member===========");
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Connection wcon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		try {

			String mTable = CstUtil.retrieveMemberTable(0);

			PreparedStatement mps = wcon
					.prepareStatement("select OriMemGUID from " + mTable);
			ResultSet mrs = mps.executeQuery();
			Set<String> memGUIDset = new HashSet<String>();
			while (mrs.next()) {
				memGUIDset.add(mrs.getString("OriMemGUID"));
			}

			ADataSource.close(mrs);
			ADataSource.close(mps);

			String timeStr = dateFormat.format(memberCfg.beginTime);
			memberCfg.beginTime = new Date(System.currentTimeMillis());
			String now = dateFormat.format(memberCfg.beginTime);

			String querySql = "SELECT " + fields + "  FROM " + table;
			querySql += " WHERE (JoinDate > ? or ApplyDate > ? or DjDate > ? or LossDate > ? or CancelDate > ? "
					+ "or shDate > ?) "
					+ "AND JoinDate < ? "
					+ "AND ApplyDate < ? "
					+ "AND shDate < ? AND (DjDate < ? or DjDate is null) "
					+ "AND (LossDate < ? or LossDate is null) AND (CancelDate < ? or CancelDate is null) ";
			log.info("execute: " + querySql + "[" + timeStr + "]");

			PreparedStatement pstmt = con.prepareStatement(querySql);
			pstmt.setString(1, timeStr);
			pstmt.setString(2, timeStr);
			pstmt.setString(3, timeStr);
			pstmt.setString(4, timeStr);
			pstmt.setString(5, timeStr);
			pstmt.setString(6, timeStr);
			pstmt.setString(7, now);
			pstmt.setString(8, now);
			pstmt.setString(9, now);
			pstmt.setString(10, now);
			pstmt.setString(11, now);
			pstmt.setString(12, now);

			ResultSet rs = pstmt.executeQuery();
			int count = 0;
			if (!rs.next()) {
				log.info("no h_Member is created");
				ADataSource.close(rs);
				ADataSource.close(pstmt);
				return;
			}

			do {
				MemInfo member = pickMemInfo(rs, wcon);

				long guid = member.getCstGUID();

				boolean flag = false;
				String tb = CstUtil.retrieveMemberTable(guid);
				PreparedStatement dataps = wcon
						.prepareStatement("select OriMemGUID from " + tb
								+ " where OriMemGUID = ?");
				dataps.setString(1, member.getOriMemGUID());
				ResultSet datars = dataps.executeQuery();
				if (datars.next()) {
					flag = true;
				}

				String insertSql = genSql(member.getCstGUID());
				PreparedStatement wpstmt = wcon.prepareStatement(insertSql);

				PreparedStatement dataUpdatePs = wcon
						.prepareStatement(getUpdateSql(member.getCstGUID()));
				saveMemInfo(member, wpstmt, flag, dataUpdatePs);

				ADataSource.close(datars);
				ADataSource.close(dataps);
				ADataSource.close(wpstmt);

				count++;

				if (guid > 0) {
					try {
						if (memGUIDset.contains(member.getOriMemGUID())) {
							PreparedStatement tempps = wcon
									.prepareStatement("delete from " + mTable
											+ " where OriMemGUID = ?");
							tempps.setString(1, member.getOriMemGUID());
							tempps.executeUpdate();
							log.info(member.getOriMemGUID()
									+ " of member-relationship has been found");
							ADataSource.close(tempps);
						}
					} catch (Exception e) {
						log.error("delete unrelation member["
								+ member.getOriMemGUID() + "] error: " + e);
						//						wcon.rollback();
					}
				}

			} while (rs.next());

			Cfg.set(memberCfg.cfgKey, now);
			Cfg.save();

			// 关闭
			ADataSource.close(rs);
			ADataSource.close(pstmt);

			log.info(count + " h_Member synced");
			log.info("=============sync h_Member end===========");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(con);
			ADataSource.close(wcon);
		}
	} // ~sync

	String genSql(long guid) {
		String table = CstUtil.retrieveMemberTable(guid);
		String fields = "OriMemGUID,"         //
				+ "CstGUID,"            //
				+ "OriCstGUID,"         //
				+ "BuGUID,"             //
				+ "MemCode,"            //
				+ "Logpwd,"             //
				+ "MemStation,"         //
				+ "SpecialType,"        //
				+ "JoinPath,"           //
				+ "JoinDate,"           //
				+ "IsSendJoinNote,"     //
				+ "LjPoint,"            //
				+ "ApplyDate,"          //
				+ "Shr,"                //
				+ "shDate,"             //
				+ "CardStatus,"         //
				+ "MakeCardReason,"     //
				+ "PostCardDate,"       //
				+ "Tjr,"                //
				+ "TjrMemCode,"         //
				+ "TjrRelation,"        //
				+ "TjRemark,"           //
				+ "ProjName,"           //
				+ "XckID,"              //
				+ "XckPwd,"             //
				+ "Saler,"              //
				+ "GetMemLevelWay,"     //
				+ "HoldLevelBeginDate," //
				+ "HoldLevelEndDate,"   //
				+ "oldMemCode,"         //
				+ "MakeCardDate,"       //
				+ "MemLevel,"           //
				+ "MaxMemLevel,"        //
				+ "MemStatus,"          //
				+ "StatusDate,"         //
				+ "StatusReason,"       //
				+ "StatusHr,"           //
				+ "OldStatus,"          //
				+ "TelList";            //
		String sql = CstUtil.formatInsertSql(table, fields);
		return sql;
	}

	String getUpdateSql(long guid) {
		String table = CstUtil.retrieveMemberTable(guid);
		String fields = "OriMemGUID,"         //
				+ "CstGUID,"            //
				+ "OriCstGUID,"         //
				+ "BuGUID,"             //
				+ "MemCode,"            //
				+ "Logpwd,"             //
				+ "MemStation,"         //
				+ "SpecialType,"        //
				+ "JoinPath,"           //
				+ "JoinDate,"           //
				+ "IsSendJoinNote,"     //
				+ "LjPoint,"            //
				+ "ApplyDate,"          //
				+ "Shr,"                //
				+ "shDate,"             //
				+ "CardStatus,"         //
				+ "MakeCardReason,"     //
				+ "PostCardDate,"       //
				+ "Tjr,"                //
				+ "TjrMemCode,"         //
				+ "TjrRelation,"        //
				+ "TjRemark,"           //
				+ "ProjName,"           //
				+ "XckID,"              //
				+ "XckPwd,"             //
				+ "Saler,"              //
				+ "GetMemLevelWay,"     //
				+ "HoldLevelBeginDate," //
				+ "HoldLevelEndDate,"   //
				+ "oldMemCode,"         //
				+ "MakeCardDate,"       //
				+ "MemLevel,"           //
				+ "MaxMemLevel,"        //
				+ "MemStatus,"          //
				+ "StatusDate,"         //
				+ "StatusReason,"       //
				+ "StatusHr,"           //
				+ "OldStatus,"          //
				+ "TelList";            //
		String sql = CstUtil.formatUpdateSql(table, fields);
		return sql + " where OriMemGUID = ? ";
	}

	private void saveMemInfo(MemInfo m, PreparedStatement pstmt, boolean flag,
			PreparedStatement dataUpdatePs) throws SQLException {

		Object[] vals = { m.getOriMemGUID(), m.getCstGUID(), m.getOriCstGUID(),
				m.getBuGUID(), m.getMemCode(), m.getLogpwd(),
				m.getMemStation(), m.getSpecialType(), m.getJoinPath(),
				m.getJoinDate(), m.getIsSendJoinNote(), m.getLjPoint(),
				m.getApplyDate(), m.getShr(), m.getShDate(), m.getCardStatus(),
				m.getMakeCardReason(), m.getPostCardDate(), m.getTjr(),
				m.getTjrMemCode(), m.getTjrRelation(), m.getTjRemark(),
				m.getProjName(), m.getXckID(), m.getXckPwd(), m.getSaler(),
				m.getGetMemLevelWay(), m.getHoldLevelBeginDate(),
				m.getHoldLevelEndDate(), m.getOldMemCode(),
				m.getMakeCardDate(), m.getMemLevel(), m.getMaxMemLevel(),
				m.getMemStatus(), m.getStatusDate(), m.getStatusReason(),
				m.getStatusHr(), m.getOldStatus(), m.getTelList() };
		try {

			for (int i = 0; i < vals.length; i++) {
				pstmt.setObject(i + 1, vals[i]);
				dataUpdatePs.setObject(i + 1, vals[i]);
			}
			if (flag) {
				dataUpdatePs.setString(40, m.getOriMemGUID().trim());
				dataUpdatePs.executeUpdate();
			} else {
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	} // ~saveMemInfo

	MemInfo pickMemInfo(ResultSet rs, Connection wcon) throws SQLException {
		MemInfo mem = new MemInfo();
		int col = 1;
		mem.setOriMemGUID(rs.getString(col++));// MemGUID,
		mem.setOriCstGUID(rs.getString(col++));// CstGUID," //

		String oriCstGUID = rs.getString("cstGUID");
		long uid = CstUtil.getCstGUIDByOriGuid(wcon, oriCstGUID);
		if (uid == 0) {
			uid = CstUtil.retrieveMemship(oriCstGUID);
		}

		mem.setCstGUID(uid);

		mem.setBuGUID(rs.getString(col++));// BUGUID,"
		mem.setMemCode(rs.getString(col++));// MemCode," //
		mem.setLogpwd(rs.getString(col++));// Logpwd," //
		mem.setMemStation(rs.getString(col++));// MemStation," //
		mem.setSpecialType(rs.getString(col++));// SpecialType," //
		mem.setJoinPath(rs.getString(col++));// JoinPath," //
		mem.setJoinDate(rs.getTimestamp(col++));// JoinDate," //
		mem.setIsSendJoinNote(rs.getInt(col++));// IsSendJoinNote," //
		Timestamp cancelDate = rs.getTimestamp(col++);// CancelDate,"
														// //
		String cancelReason = rs.getString(col++); // CancelReason," //
		mem.setLjPoint(rs.getFloat(col++));// LjPoint," //
		mem.setMaxMemLevel(rs.getString(col++));// MaxMemlevel," //
		String status = rs.getString(col++);
		mem.setOldStatus(status);
		mem.setApplyDate(rs.getTimestamp(col++));// ApplyDate,"
													// //
		mem.setShr(rs.getString(col++));// shr," //
		mem.setShDate(rs.getTimestamp(col++));// ShDate," //
		mem.setCardStatus(rs.getString(col++));// CardStatus," //
		mem.setMakeCardReason(rs.getString(col++));// MakeCardReason," //
		Timestamp lossDate = rs.getTimestamp(col++);// LossDate," //
		String lossReason = rs.getString(col++);// LossReason," //
		String lossJbr = rs.getString(col++);// LossJbr," //
		mem.setTjr(rs.getString(col++));// Tjr," //
		mem.setTjrMemCode(rs.getString(col++));// TjrMemCode," //
		mem.setTjrRelation(rs.getString(col++));// TjrRelation," //
		mem.setTjRemark(rs.getString(col++));// TjReMark," //
		mem.setProjName(rs.getString(col++));// ProjName," //
		mem.setMemLevel(rs.getString(col++));// MemLevel," //
		mem.setSaler(rs.getString(col++));// Saler," //
		mem.setXckID(rs.getString(col++));// XckID," //
		mem.setGetMemLevelWay(rs.getString(col++));// GetMemLevelWay," //
		mem.setHoldLevelBeginDate(rs.getTimestamp(col++));// HoldLevelBgnDate,"
															// //
		mem.setHoldLevelEndDate(rs.getTimestamp(col++));// HoldLevelEndDate,"
														// //
		mem.setOldMemCode(rs.getString(col++));// oldMemCode," //
		rs.getString(col++);// Is_Dywkzk," //
		rs.getString(col++);// Wkzkdynum," //
		rs.getString(col++);// Wkkdystart," //
		rs.getString(col++);// Wkkdyend," //
		rs.getString(col++);// Lostcard," //
		rs.getString(col++);// Givedate," //
		rs.getString(col++);// Is_notmessage," //
		rs.getString(col++);// oldid," //
		mem.setMakeCardDate(rs.getTimestamp(col++));// MakeCardDate,"
													// //
		mem.setPostCardDate(rs.getTimestamp(col++));// PostCardDate,"
													// //
		rs.getString(col++);// is_notpos," //
		rs.getString(col++);// notpost_yy," //
		String djr = rs.getString(col++);// Djr," //
		Timestamp djDate = rs.getTimestamp(col++);// DjDate," //
		String djReason = rs.getString(col++);// DjReason," //
		mem.setXckPwd(rs.getString(col++));// XckPwd," //
		rs.getString(col++);// FirstLxfs," //
		mem.setTelList(rs.getString(col++));// TelList," //
		rs.getString(col++);// QxStatus"; //

		// 1 会员lossdate不为空的，为卡丢失的，会员状态为卡丢失；loss相关字段丢弃，
		// 原memstatus是“退会员客户的为取消
		// 2 lostcard没用
		// 3 is_dywkzk为1的五条，仅一条有始末时间；暂不生成订购关系
		// 4 dywkzk相关字段丢弃
		// 5 djdate不为空的，为冻结；状态为冻结；
		// 6 业务库
		// memGUID自增主键，无任何意义
		// 原memGUID保留为OriMemGUID
		// CstGUID改为数据中心生成的UID，类型改为bigint
		// BuGUID保留名称与类型

		mem.setMemStatus(1);
		mem.setStatusHr("");
		if ("退会客户".equals(status)) {
			mem.setMemStatus(0); // 退会员
			mem.setStatusReason(cancelReason);
			mem.setStatusDate(cancelDate);
		} else if (lossDate != null) {
			mem.setMemStatus(2); // 卡丢失
			mem.setStatusDate(lossDate);
			mem.setStatusReason(lossReason);
			mem.setStatusHr(lossJbr);
		} else if (djDate != null) {
			mem.setMemStatus(3);
			mem.setStatusDate(djDate);
			mem.setStatusReason(djReason);
			mem.setStatusHr(djr);
		}

		return mem;
	}

}
